package com.supermap.dataservice.farmland.dao;

import com.supermap.dubbo.api.farmland.entity.Farmland;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @Author:langhongshuai
 * @Description:农田数据访问层
 * @Date:18/1/28 下午10:52
 */
@Mapper
public interface FarmlandDao {
    /**
     * 获得用户的所有农田信息
     * @return
     */
    @Select("select t1.*,t6.areaname,t4.name,t4.crop,t5.username,t4.parentid as parentcrop from t_farmland t1 " +
            " inner JOIN t_area t6 on t1.`code`=t6.areacode "+
            "inner JOIN t_user t5 on t1.userId=t5.id inner JOIN" +
            "(select t2.*,t3.name,if(t3.parentid=0,t3.code,t3.parentid) as parentid from t_farmcrops t2 left JOIN t_crop t3 on t2.crop=t3.code) t4\n" +
            "on t1.id=t4.farmland where IF(isnull(#{userId}),true,t1.userId=#{userId}) and IF(ISNULL(#{areaCode}),true,t1.code like #{areaCode}) ")
    public List<Farmland> getFarmlandByUIdArea(@Param("userId") String userId, @Param("areaCode") String areaCode);

    /**
     * 增加农田
     * @param farmland
     */
    @Insert("Insert into t_farmland(userId,coordinates,descript,height,longitude,latitude,area,code,farmname)\n " +
            "values(#{farmland.userId},#{farmland.coordinates},#{farmland.descript},\n" +
            "#{farmland.height},#{farmland.longitude},#{farmland.latitude},#{farmland.area},#{farmland.code},#{farmland.farmname} \n" +
            ")")
    public void addFarmland(@Param("farmland") Farmland farmland);

    /**
     * 增加农田作物关联
     * @param crop
     * @param farmland
     */
    @Insert("Insert into t_farmcrops(crop,farmland) values(#{crop},#{farmland})")
    public void addFarmCrops(@Param("farmland") int farmland, @Param("crop") String crop);

    /**
     * 获取刚刚插入的id
     */
    @Select("Select max(id) from t_farmland")
    public int getFarmId();

    /**
     * 获得用户的所有农田信息
     * @return
     * @param areaCodes
     */
    @Select("<script>" +
            "SELECT COUNT(t1.id) AS count, t1.code AS areaCode,t_area.areaname AS areaName FROM t_farmland t1 \n" +
            "JOIN t_area ON t_area.areacode = t1.code\n" +
            "WHERE t1.del_flg = 0 \n" +
            "<if test='areaCodes != null'>\n" +
            "AND t1.code IN\n" +
            "<foreach collection='areaCodes' open='(' close=')' item='areaCode' separator=','>\n" +
            "#{areaCode}\n" +
            "</foreach>\n" +
            "</if>" +
            "GROUP BY t1.code" +
            "</script>")
    List<Map> getFarmlandCountByArea(@Param("areaCodes") String[] areaCodes);

    /**
     * 删除农田
     * @param farmlandId
     */
    @Delete("Delete from t_farmland where id=#{farmlandId}")
    public void delFarmland(@Param("farmlandId") int farmlandId);

    /**
     * 通过作物id和区域code获取农田信息
     * @param cropId
     * @param areaCode
     */
    @Select("SELECT t1.*, t2.crop,t3.username,t4.areaname FROM\n"
            + "t_farmland t1\n"
            + "LEFT JOIN t_farmcrops t2 ON t1.id = t2.farmland\n"
            + "LEFT JOIN t_user t3 ON t1.userId = t3.id\n"
            + "LEFT JOIN t_area t4 ON t4.areacode = t1.code\n"
            + "left join t_crop t5 on t2.crop in(select id from t_crop "
            + "where id in(select id from t_crop where parentid = t5.id or id=t5.id) "
            + "or parentid in (select id from t_crop where parentid = t5.id )) "
            + "where if((isnull(#{cropId}) or #{cropId}=''),true,t2.crop in(select id from t_crop where id in(select id from t_crop where parentid = #{cropId} or id= #{cropId}) or\n"
            + " parentid in (select id from t_crop where parentid = #{cropId}  ))) and if(isnull(#{areaCode}),true,t1.code like CONCAT(#{areaCode},'%'))")
    public List<Farmland> getFramlandByCropIdAndAreaCode(@Param("cropId") String cropId, @Param("areaCode") String areaCode);

    /**
     * 通过区域获取农田
     * @param areaCode
     * @return
     */
    @Select("select t1.*,t2.areaname from t_farmland t1 LEFT JOIN t_area t2 ON t2.areacode = t1.code where t1.code like CONCAT(#{areaCode},'%')")
    public List<Farmland> getAllFarmland(@Param("areaCode") String areaCode);


    /**
     * 获得用户的所有农田信息
     * @return
     */
    @Select("select sum(t1.area) as area,\n" +
            "\tt1.*,\n" +
            "\tt4. NAME ,\n" +
            "t4.parentid AS parentcrop ,\n" +
            "\tt4.crop," +
            "\tt5.username,\n" +
            "\tGROUP_CONCAT(t6.areaname) as areaname " +
            " from t_farmland t1 " +
            " inner JOIN t_area t6 on t1.`code`=t6.areacode "+
            "inner JOIN t_user t5 on t1.userId=t5.id inner JOIN" +
            "(select t2.*,t3.name,if(t3.parentid=0,t3.`code`,t3.parentid) as parentid from t_farmcrops t2 left JOIN t_crop t3 on t2.crop=t3.code) t4\n" +
            "on t1.id=t4.farmland where IF(isnull(#{userId}),true,t1.userId=#{userId}) group by crop")
    public List<Farmland> getFarmlandByUId(@Param("userId") String userId);


    /**
     * 更新famland信息
     * @param farmland
     */
    @Update("update t_farmland f,t_farmcrops fc set fc.updatetime=#{Farmland.updateTime},fc.crop = #{Farmland.crop},f.userId=#{Farmland.userId}, " +
            "f.workStationID =#{Farmland.workStationID},f.coordinates=#{Farmland.coordinates},f.descript=#{Farmland.descript}," +
            "f.height=#{Farmland.height},f.longitude=#{Farmland.longitude},f.latitude=#{Farmland.latitude},f.area=#{Farmland.area}," +
            "f.code=#{Farmland.code},f.farmname=#{Farmland.farmname},f.updatetime=#{Farmland.updateTime} where fc.farmland=#{Farmland.id} and f.id=#{Farmland.id}")
    public void updateFarmland(@Param("Farmland")Farmland farmland);
}
